package id.net.iconpln.bb.server.dao;

import id.net.iconpln.bb.server.util.CommonModule;

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import oracle.jdbc.OracleTypes;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

@Repository
public class GudangDao {
	@Autowired private SessionFactory sessionFactory;
	
	public List<Map> insertGudangBB(String kode, Map map) {
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();  
        try{
        	String sql = null;
            CallableStatement call = null;
            
            sql = "{call BBTRXGUDANG.SETID_PLTU(?)}";
            call = con.prepareCall(sql);
            call.setBigDecimal(1, new BigDecimal(map.get("id_pltu").toString()));
            call.execute();
            
            sql = "{call BBTRXGUDANG.SETID_UNIT(?)}";
            call = con.prepareCall(sql);
            call.setBigDecimal(1, new BigDecimal(map.get("id_unit").toString()));
            call.execute();
            
            sql = "{call BBTRXGUDANG.SETVOLUME_PENGAJUAN(?)}";
            call = con.prepareCall(sql);
            call.setBigDecimal(1, new BigDecimal(map.get("volume_pengajuan").toString()));
            call.execute();
            
            sql = "{call BBTRXGUDANG.SETKETERANGAN(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("keterangan").toString());
            call.execute();
            
            sql = "{call BBTRXGUDANG.SETSATUAN(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("satuan").toString());
            call.execute();
            
            sql = "{call BBTRXGUDANG.SETTANGGAL_KIRIM(?)}";
            call = con.prepareCall(sql);
            DateFormat df = new SimpleDateFormat("dd/mm/yyyy");
            Date tgl = df.parse(map.get("tanggal").toString());
            call.setDate(1, new java.sql.Date(tgl.getTime()));
            call.execute();            
            
            sql = "{call BBTRXGUDANG.SETCREATEBY(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("user").toString());
            call.execute();
            
            sql = "{call BBTRXGUDANG.SETAPP_ACCESS(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("app_access").toString());
            call.execute();
            
            sql = "{call BBTRXGUDANG.SETNO_PERMINTAAN(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("no_permintaan").toString());
            call.execute();
            
            if (kode.equals("RECORD_PEMAKAIAN")){
            	sql = "{call BBTRXGUDANG.SETSTART_TIME(?)}";
                call = con.prepareCall(sql);
                DateFormat df1 = new SimpleDateFormat("dd/MM/yyyy HH:mm");
                Date tglStart = df1.parse(map.get("tgl_start").toString());
                call.setTimestamp(1, new java.sql.Timestamp(tglStart.getTime()));
                call.execute();    
                                
                sql = "{call BBTRXGUDANG.SETEND_TIME(?)}";
                call = con.prepareCall(sql);
                Date tglFinish = df1.parse(map.get("tgl_finish").toString());
                call.setTimestamp(1, new java.sql.Timestamp(tglFinish.getTime()));
                call.execute();    
                
                sql = "{call BBTRXGUDANG.SETVOLUME_PEMAKAIAN(?)}";
                call = con.prepareCall(sql);
                call.setBigDecimal(1, new BigDecimal(map.get("volume_pengajuan").toString()));
                call.execute();
            }
            
            sql = "{? = call BBTRXGUDANG.INSERTGUDANGBB(?,?)}";
            call = con.prepareCall(sql);
            call.registerOutParameter(1, OracleTypes.VARCHAR);
            call.setString(2, kode);
            call.setString(3, map.get("crud").toString());            
            call.execute();
            map.put("message", call.getString(1));    
            System.out.println(call.getString(1));
        }catch (Exception e) {
        	map.put("message", e.getMessage());    
        	e.printStackTrace();
		}
        lMap.add(map);
        return lMap;
	}

	public List<Map> getListPemakaian(String kode, Map map) {
		// TODO Auto-generated method stub
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        try {
            String sql = null;
            CallableStatement call;            
            if (kode.equals("PEMAKAIAN")){
            	sql = "{call BBTRXGUDANG.SETID_PLTU(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("id_pltu").toString());
                call.execute();
                
                sql = "{call BBTRXGUDANG.SETBLTH(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("blth").toString());
                call.execute();
                
                sql = "{call BBTRXGUDANG.SETID_UNIT(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("id_unit").toString());
                call.execute();
            }else if (kode.equals("LIST_STOK") || kode.equals("LIST_PEMAKAIAN")) {
            	sql = "{call BBTRXGUDANG.SETID_PLTU(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("id_pltu").toString());
                call.execute();
            }
            
            sql = "{? = call BBTRXGUDANG.GETLISTGUDANGBY(?)}";
            call = con.prepareCall(sql);
            call.registerOutParameter(1, OracleTypes.CURSOR);
            call.setString(2, kode);
            call.execute();
            ResultSet rset = (ResultSet) call.getObject(1);
            lMap = CommonModule.convertResultsetToListLower(rset);
        }catch (Exception e) {
			e.printStackTrace();
		}        
        return lMap;
	}
	
}
